{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# 一、获取数据\n",
    "\n",
    "## 1.1 新建数据框\n",
    "\n",
    "DataFrame数据框架对象实际上是二维表，类似于关系型数据库和excel表格。\n",
    "\n",
    "语法：\n",
    "\n",
    "``` python\n",
    "pd.DataFrame(\n",
    "    data = None, # 数据列表，字典格式时直接同时提供变量名\n",
    "    columns = None, #变量名列表\n",
    ")\n",
    "```"
   ],
   "id": "c2c9367518b21da5"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T08:48:22.651538Z",
     "start_time": "2025-09-28T08:48:20.665485Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 通过字典创建二维表\n",
    "# 这个字典的var1和var4缺失了一些数据，pd会自动填充\n",
    "# 但是不建议使用这种写法\n",
    "df1 = pd.DataFrame(\n",
    "{\n",
    "'var1' : 1.0,\n",
    "'var2' : [1,2,3,4],\n",
    "'var3' :[\"test\",\"train\",\"test\",\"train\"],\n",
    "'var4' : 'cons'\n",
    "}\n",
    ")\n",
    "\n",
    "df1"
   ],
   "id": "f9048410394cd499",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   var1  var2   var3  var4\n",
       "0   1.0     1   test  cons\n",
       "1   1.0     2  train  cons\n",
       "2   1.0     3   test  cons\n",
       "3   1.0     4  train  cons"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>var1</th>\n",
       "      <th>var2</th>\n",
       "      <th>var3</th>\n",
       "      <th>var4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "      <td>cons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "      <td>cons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "      <td>cons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "      <td>cons</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T08:52:20.139832Z",
     "start_time": "2025-09-28T08:52:20.131829Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 通过列表创建数据框架对象\n",
    "# 以list形式按行提供数据\n",
    "df1 = pd.DataFrame(data = [\n",
    "        [1,\"test\"], [2,\"train\"],\n",
    "        [3,\"test\"],[4,\"train\"]\n",
    "    ],\n",
    "    columns = [ 'var2', 'var3' ]\n",
    ")\n",
    "df1"
   ],
   "id": "ed7acd95c56f1f4e",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   var2   var3\n",
       "0     1   test\n",
       "1     2  train\n",
       "2     3   test\n",
       "3     4  train"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>var2</th>\n",
       "      <th>var3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>train</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Series序列\n",
    "\n",
    "DataFrame的每一列都是一个Series序列"
   ],
   "id": "928133bb1c1e04b3"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T08:55:37.260646Z",
     "start_time": "2025-09-28T08:55:37.255004Z"
    }
   },
   "cell_type": "code",
   "source": [
    "print(type(df1.var3))\n",
    "df1.var3"
   ],
   "id": "9c4f0943dfd706b8",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0     test\n",
       "1    train\n",
       "2     test\n",
       "3    train\n",
       "Name: var3, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 7
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T08:55:08.467900Z",
     "start_time": "2025-09-28T08:55:08.464170Z"
    }
   },
   "cell_type": "code",
   "source": [
    "s1 = pd.Series([\"test\",\"train\",\"test\",\"train\"])\n",
    "s1"
   ],
   "id": "813169a489741d01",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     test\n",
       "1    train\n",
       "2     test\n",
       "3    train\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T08:54:47.479039Z",
     "start_time": "2025-09-28T08:54:47.475056Z"
    }
   },
   "cell_type": "code",
   "source": [
    "s2 = pd.Series(data = [\"test\",\"train\",\"test\",\"train\"], name = 'var3')\n",
    "s2"
   ],
   "id": "2a27a3e068df1265",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     test\n",
       "1    train\n",
       "2     test\n",
       "3    train\n",
       "Name: var3, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## 1.2 读取csv表格或文本文件txt\n",
    "\n",
    "语法：\n",
    "\n",
    "``` python\n",
    "pd.read_csv(\n",
    "    filepath_or_buffer, # 要读入的文件路径\n",
    "    sep = ',', # 列分隔符，依靠这个参数划分单元格\n",
    "\n",
    "    header = 'infer', # 指定数据中的第几行作为变量名\n",
    "    names = None, # 自定义变量名列表\n",
    "    index_col = None ：将会被用作索引的列名，多列时只能使用序号列表\n",
    "    usecols = None ：指定只读入某些列，使用索引列表或者名称列表均可\n",
    "        [0,1,3], [\"名次\", \"学校名称\", \"所在地区\"]\n",
    "    encoding = None, # 读入文件的编码方式utf-8/GBK中文数据文件最好设定为utf-8\n",
    "    encoding_errors = 'strict' : 1.3版新增，决定因编码设定不正确导致读错误时的处理方式\n",
    "        'strict' : 直接报错，抛出 UnicodeError (or a subclass)\n",
    "        'ignore' : 忽视错误继续执行\n",
    "    na_values ：指定将被读入为缺失值的数值列表，默认下列数据被读入为缺失值:\n",
    "        '', '#N/A', '#N/A N/A', '#NA', '-1.#IND',\n",
    "        '-1.#QNAN', '-NaN', '-nan', '1.#IND', '1.#QNAN',\n",
    "        'N/A', 'NA', 'NULL', 'NaN', 'n/a', 'nan', 'null'\n",
    ")\n",
    "```\n",
    "\n",
    "读取csv格式文件，但也可通用于文本文件读取，也可使用pd.read_table() 主要的区别在于默认的sep=\"\\t\"，即tab符号。"
   ],
   "id": "98ad2a290575372"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:09:05.089328Z",
     "start_time": "2025-09-28T09:09:05.064615Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取csv表格\n",
    "df2 = pd.read_csv('univ.csv', encoding='gbk')\n",
    "df2 # 默认显示前五行和后五行"
   ],
   "id": "724bb213444359d0",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "     名次    学校名称      总分  类型 所在省份 所在城市     办学方向 主管部门\n",
       "0     1    北京大学  100.00  综合   北京  北京市    中国研究型  教育部\n",
       "1     2    清华大学   98.50  理工   北京  北京市    中国研究型  教育部\n",
       "2     3    复旦大学   82.79  综合   上海  上海市    中国研究型  教育部\n",
       "3     4    武汉大学   82.43  综合   湖北  武汉市    中国研究型  教育部\n",
       "4     5    浙江大学   82.38  综合   浙江  杭州市    中国研究型  教育部\n",
       "..  ...     ...     ...  ..  ...  ...      ...  ...\n",
       "95   96  浙江师范大学   63.37  师范   浙江  金华市  区域特色研究型  浙江省\n",
       "96   97    安徽大学   63.34  综合   安徽  合肥市    区域研究型  安徽省\n",
       "97   98  首都医科大学   63.32  医药   北京  北京市  区域特色研究型  北京市\n",
       "98   99    江南大学   63.31  综合   江苏  无锡市  区域特色研究型  教育部\n",
       "99  100    山西大学   63.29  综合   山西  太原市    区域研究型  山西省\n",
       "\n",
       "[100 rows x 8 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>学校名称</th>\n",
       "      <th>总分</th>\n",
       "      <th>类型</th>\n",
       "      <th>所在省份</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>办学方向</th>\n",
       "      <th>主管部门</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>北京大学</td>\n",
       "      <td>100.00</td>\n",
       "      <td>综合</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>清华大学</td>\n",
       "      <td>98.50</td>\n",
       "      <td>理工</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>复旦大学</td>\n",
       "      <td>82.79</td>\n",
       "      <td>综合</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>武汉大学</td>\n",
       "      <td>82.43</td>\n",
       "      <td>综合</td>\n",
       "      <td>湖北</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>浙江大学</td>\n",
       "      <td>82.38</td>\n",
       "      <td>综合</td>\n",
       "      <td>浙江</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>浙江师范大学</td>\n",
       "      <td>63.37</td>\n",
       "      <td>师范</td>\n",
       "      <td>浙江</td>\n",
       "      <td>金华市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>安徽大学</td>\n",
       "      <td>63.34</td>\n",
       "      <td>综合</td>\n",
       "      <td>安徽</td>\n",
       "      <td>合肥市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>安徽省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>首都医科大学</td>\n",
       "      <td>63.32</td>\n",
       "      <td>医药</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>北京市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>江南大学</td>\n",
       "      <td>63.31</td>\n",
       "      <td>综合</td>\n",
       "      <td>江苏</td>\n",
       "      <td>无锡市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>山西大学</td>\n",
       "      <td>63.29</td>\n",
       "      <td>综合</td>\n",
       "      <td>山西</td>\n",
       "      <td>太原市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 8 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:11:16.761028Z",
     "start_time": "2025-09-28T09:11:16.753726Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 使用read_table读取csv表格\n",
    "# 必须指定sep=','，要不然每行作为一个单元格\n",
    "df2 = pd.read_table('univ.csv', encoding='gbk', sep=',')\n",
    "df2"
   ],
   "id": "9b15e58442ea689d",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "     名次    学校名称      总分  类型 所在省份 所在城市     办学方向 主管部门\n",
       "0     1    北京大学  100.00  综合   北京  北京市    中国研究型  教育部\n",
       "1     2    清华大学   98.50  理工   北京  北京市    中国研究型  教育部\n",
       "2     3    复旦大学   82.79  综合   上海  上海市    中国研究型  教育部\n",
       "3     4    武汉大学   82.43  综合   湖北  武汉市    中国研究型  教育部\n",
       "4     5    浙江大学   82.38  综合   浙江  杭州市    中国研究型  教育部\n",
       "..  ...     ...     ...  ..  ...  ...      ...  ...\n",
       "95   96  浙江师范大学   63.37  师范   浙江  金华市  区域特色研究型  浙江省\n",
       "96   97    安徽大学   63.34  综合   安徽  合肥市    区域研究型  安徽省\n",
       "97   98  首都医科大学   63.32  医药   北京  北京市  区域特色研究型  北京市\n",
       "98   99    江南大学   63.31  综合   江苏  无锡市  区域特色研究型  教育部\n",
       "99  100    山西大学   63.29  综合   山西  太原市    区域研究型  山西省\n",
       "\n",
       "[100 rows x 8 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>学校名称</th>\n",
       "      <th>总分</th>\n",
       "      <th>类型</th>\n",
       "      <th>所在省份</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>办学方向</th>\n",
       "      <th>主管部门</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>北京大学</td>\n",
       "      <td>100.00</td>\n",
       "      <td>综合</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>清华大学</td>\n",
       "      <td>98.50</td>\n",
       "      <td>理工</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>复旦大学</td>\n",
       "      <td>82.79</td>\n",
       "      <td>综合</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>武汉大学</td>\n",
       "      <td>82.43</td>\n",
       "      <td>综合</td>\n",
       "      <td>湖北</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>浙江大学</td>\n",
       "      <td>82.38</td>\n",
       "      <td>综合</td>\n",
       "      <td>浙江</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>浙江师范大学</td>\n",
       "      <td>63.37</td>\n",
       "      <td>师范</td>\n",
       "      <td>浙江</td>\n",
       "      <td>金华市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>安徽大学</td>\n",
       "      <td>63.34</td>\n",
       "      <td>综合</td>\n",
       "      <td>安徽</td>\n",
       "      <td>合肥市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>安徽省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>首都医科大学</td>\n",
       "      <td>63.32</td>\n",
       "      <td>医药</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>北京市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>江南大学</td>\n",
       "      <td>63.31</td>\n",
       "      <td>综合</td>\n",
       "      <td>江苏</td>\n",
       "      <td>无锡市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>山西大学</td>\n",
       "      <td>63.29</td>\n",
       "      <td>综合</td>\n",
       "      <td>山西</td>\n",
       "      <td>太原市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 8 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:12:49.605021Z",
     "start_time": "2025-09-28T09:12:49.598364Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 汇总非缺失值的数量\n",
    "df2.agg('count')"
   ],
   "id": "b9dc92e7baf6ecbb",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "名次      100\n",
       "学校名称    100\n",
       "总分      100\n",
       "类型      100\n",
       "所在省份    100\n",
       "所在城市    100\n",
       "办学方向    100\n",
       "主管部门    100\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 11
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:14:34.873901Z",
     "start_time": "2025-09-28T09:14:34.871532Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 显示当前工作目录\n",
    "import os\n",
    "\n",
    "current_path = os.getcwd()\n",
    "print(\"当前工作目录:\", current_path)"
   ],
   "id": "ac8de7dd1b5fe46f",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前工作目录: C:\\Users\\d111k\\Desktop\\data\\data-analysis\\notebook\\pd\n"
     ]
    }
   ],
   "execution_count": 12
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## 1.3 读取excel文件\n",
    "\n",
    "一个excel文件又叫一个工作簿，一个工作簿中有多个工作表，read_excel默认读取的是excel文件中的第一个工作表\n",
    "\n",
    "语法：\n",
    "\n",
    "``` python\n",
    "pd.read_excel(\n",
    "    filepath_or_buffer, # 要读入的文件路径\n",
    "    sheet_name, # 要读入的工作表，表名字符串或者数字序号均可，默认读入第一个（序号为0）\n",
    "    engine, # 3.0中提供了\"calamine\"引擎，读入速度很快\n",
    ")\n",
    "```"
   ],
   "id": "9a12766e6487e81"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:23:11.508260Z",
     "start_time": "2025-09-28T09:23:11.062436Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取一个工作表\n",
    "df2 = pd.read_excel('univ.xlsx')\n",
    "df2 # 读取的是full工作表"
   ],
   "id": "4d8b9f49cea84814",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "     名次    学校名称      总分  类型 所在省份 所在城市     办学方向 主管部门\n",
       "0     1    北京大学  100.00  综合   北京  北京市    中国研究型  教育部\n",
       "1     2    清华大学   98.50  理工   北京  北京市    中国研究型  教育部\n",
       "2     3    复旦大学   82.79  综合   上海  上海市    中国研究型  教育部\n",
       "3     4    武汉大学   82.43  综合   湖北  武汉市    中国研究型  教育部\n",
       "4     5    浙江大学   82.38  综合   浙江  杭州市    中国研究型  教育部\n",
       "..  ...     ...     ...  ..  ...  ...      ...  ...\n",
       "95   96  浙江师范大学   63.37  师范   浙江  金华市  区域特色研究型  浙江省\n",
       "96   97    安徽大学   63.34  综合   安徽  合肥市    区域研究型  安徽省\n",
       "97   98  首都医科大学   63.32  医药   北京  北京市  区域特色研究型  北京市\n",
       "98   99    江南大学   63.31  综合   江苏  无锡市  区域特色研究型  教育部\n",
       "99  100    山西大学   63.29  综合   山西  太原市    区域研究型  山西省\n",
       "\n",
       "[100 rows x 8 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>学校名称</th>\n",
       "      <th>总分</th>\n",
       "      <th>类型</th>\n",
       "      <th>所在省份</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>办学方向</th>\n",
       "      <th>主管部门</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>北京大学</td>\n",
       "      <td>100.00</td>\n",
       "      <td>综合</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>清华大学</td>\n",
       "      <td>98.50</td>\n",
       "      <td>理工</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>复旦大学</td>\n",
       "      <td>82.79</td>\n",
       "      <td>综合</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>武汉大学</td>\n",
       "      <td>82.43</td>\n",
       "      <td>综合</td>\n",
       "      <td>湖北</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>浙江大学</td>\n",
       "      <td>82.38</td>\n",
       "      <td>综合</td>\n",
       "      <td>浙江</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>中国研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>浙江师范大学</td>\n",
       "      <td>63.37</td>\n",
       "      <td>师范</td>\n",
       "      <td>浙江</td>\n",
       "      <td>金华市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>安徽大学</td>\n",
       "      <td>63.34</td>\n",
       "      <td>综合</td>\n",
       "      <td>安徽</td>\n",
       "      <td>合肥市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>安徽省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>首都医科大学</td>\n",
       "      <td>63.32</td>\n",
       "      <td>医药</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>北京市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>江南大学</td>\n",
       "      <td>63.31</td>\n",
       "      <td>综合</td>\n",
       "      <td>江苏</td>\n",
       "      <td>无锡市</td>\n",
       "      <td>区域特色研究型</td>\n",
       "      <td>教育部</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>山西大学</td>\n",
       "      <td>63.29</td>\n",
       "      <td>综合</td>\n",
       "      <td>山西</td>\n",
       "      <td>太原市</td>\n",
       "      <td>区域研究型</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 8 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:26:00.715603Z",
     "start_time": "2025-09-28T09:26:00.674282Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取所有工作表\n",
    "# 此时read_excel返回工作簿字典，\n",
    "# key是工作表名称\n",
    "# value是每个工作表的df对象\n",
    "dfdict = pd.read_excel('univ.xlsx', sheet_name=None)\n",
    "type(dfdict)\n",
    "sheet_names = list(dfdict.keys())\n",
    "sheet_names"
   ],
   "id": "74eeed456bc54772",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['full', 'part1', 'part2', 'var6', 'var3']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 18
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-28T09:29:00.610337Z",
     "start_time": "2025-09-28T09:29:00.603206Z"
    }
   },
   "cell_type": "code",
   "source": [
    "for df in dfdict.values():\n",
    "    print('-----------')\n",
    "    print(df.head(5))"
   ],
   "id": "89a17789decc48f9",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------\n",
      "   名次  学校名称      总分  类型 所在省份 所在城市   办学方向 主管部门\n",
      "0   1  北京大学  100.00  综合   北京  北京市  中国研究型  教育部\n",
      "1   2  清华大学   98.50  理工   北京  北京市  中国研究型  教育部\n",
      "2   3  复旦大学   82.79  综合   上海  上海市  中国研究型  教育部\n",
      "3   4  武汉大学   82.43  综合   湖北  武汉市  中国研究型  教育部\n",
      "4   5  浙江大学   82.38  综合   浙江  杭州市  中国研究型  教育部\n",
      "-----------\n",
      "   名次  学校名称      总分  类型 所在省份 所在城市   办学方向 主管部门\n",
      "0   1  北京大学  100.00  综合   北京  北京市  中国研究型  教育部\n",
      "1   2  清华大学   98.50  理工   北京  北京市  中国研究型  教育部\n",
      "2   3  复旦大学   82.79  综合   上海  上海市  中国研究型  教育部\n",
      "3   4  武汉大学   82.43  综合   湖北  武汉市  中国研究型  教育部\n",
      "4   5  浙江大学   82.38  综合   浙江  杭州市  中国研究型  教育部\n",
      "-----------\n",
      "   名次       学校名称     总分  类型 所在省份 所在城市     办学方向 主管部门\n",
      "0  51     西南交通大学  65.67  理工   四川  成都市  行业特色研究型  教育部\n",
      "1  52       西南大学  65.64  师范   重庆  重庆市    区域研究型  教育部\n",
      "2  53     中国海洋大学  65.56  综合   山东  青岛市  行业特色研究型  教育部\n",
      "3  54       河海大学  65.50  理工   江苏  南京市  行业特色研究型  教育部\n",
      "4  55  解放军信息工程大学  65.49  理工   河南   郑州  行业特色研究型     \n",
      "-----------\n",
      "   名次  学校名称      总分  类型 所在省份 所在城市\n",
      "0   1  北京大学  100.00  综合   北京  北京市\n",
      "1   2  清华大学   98.50  理工   北京  北京市\n",
      "2   3  复旦大学   82.79  综合   上海  上海市\n",
      "3   4  武汉大学   82.43  综合   湖北  武汉市\n",
      "4   5  浙江大学   82.38  综合   浙江  杭州市\n",
      "-----------\n",
      "   学校名称   办学方向 主管部门\n",
      "0  北京大学  中国研究型  教育部\n",
      "1  清华大学  中国研究型  教育部\n",
      "2  复旦大学  中国研究型  教育部\n",
      "3  武汉大学  中国研究型  教育部\n",
      "4  浙江大学  中国研究型  教育部\n"
     ]
    }
   ],
   "execution_count": 23
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
